﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.ObjectModel

Public Class DbHelper

    Shared Function GetConnectionString()
        Dim constr As String = ConfigurationManager.ConnectionStrings("msgweb_dbconnection").ToString
        Return constr
    End Function

#Region "Users"
    Public Function GetUserById(UserId As Guid) As UserType

        Dim dt As New DataTable
        Dim res As New UserType
        Dim cmd As New SqlCommand("SELECT u.*,r.RightsName " & _
                                  "FROM [Users] AS u LEFT OUTER JOIN UsersRights AS r ON (u.[UserId]=r.[UserId]) " & _
                                  "WHERE u.UserId ='" & UserId.ToString & "'")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            For Each row As DataRow In dt.Rows
                res.UserId = row("UserId")
                If IsDBNull(row("ProviderUserKey")) Then
                    res.ProviderUserKey = ""
                Else
                    res.ProviderUserKey = row("ProviderUserKey")
                End If
                res.UserName = row("UserName")
                If row("RightsName").ToString <> "" Then
                    res.Rights.Add(row("RightsName"))
                End If
            Next
            con.Close()
        End Using

        Return res

    End Function
    Public Function GetUserByName(UserName As String) As UserType

        Dim dt As New DataTable
        Dim res As New UserType
        Dim cmd As New SqlCommand("SELECT u.*,r.RightsName " & _
                                  "FROM [Users] AS u INNER JOIN UsersRights AS r ON (u.[UserId]=r.[UserId]) " & _
                                  "WHERE u.UserName='" & UserName & "'")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            For Each row As DataRow In dt.Rows
                res.UserId = row("UserId")
                If IsDBNull(row("ProviderUserKey")) Then
                    res.ProviderUserKey = ""
                Else
                    res.ProviderUserKey = row("ProviderUserKey")
                End If
                res.UserName = row("UserName")
                res.Rights.Add(row("RightsName"))
            Next
            con.Close()
        End Using

        If res.UserId = Guid.Empty Then Return Nothing
        If res.UserName = "" Then Return Nothing

        Return res

    End Function
    Public Function GetUsersList() As Collection(Of UserType)

        Dim dt As New DataTable
        Dim res As New Collection(Of UserType)
        Dim cmd As New SqlCommand("SELECT * FROM [Users] ORDER BY UserName")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim u As New UserType
                    u.UserId = row("UserId")
                    If IsDBNull(row("ProviderUserKey")) Then
                        u.ProviderUserKey = ""
                    Else
                        u.ProviderUserKey = row("ProviderUserKey")
                    End If
                    u.UserName = row("UserName")
                    Dim dt2 As New DataTable
                    Dim cmd2 As New SqlCommand("SELECT * FROM [UsersRights] WHERE UserId='" & u.UserId.ToString & "' ORDER BY [RightsName]")
                    cmd2.Connection = con
                    da = New SqlDataAdapter(cmd2)
                    da.Fill(dt2)
                    If dt2.Rows.Count > 0 Then
                        For i2 As Integer = 0 To dt2.Rows.Count - 1
                            Dim row2 As DataRow = dt2.Rows(i2)
                            u.Rights.Add(row2("RightsName"))
                        Next
                    End If
                    res.Add(u)
                Next
            End If
            con.Close()
        End Using

        Return res

    End Function
    Public Function GetUsersListAuthorizable() As Collection(Of UserType)

        Dim dt As New DataTable
        Dim res As New Collection(Of UserType)
        Dim cmd As New SqlCommand("SELECT u.* " & _
                                  "FROM [Users] AS u " & _
                                  "     INNER JOIN UsersRights AS ur ON (u.UserId = ur.UserId) " & _
                                  "                         AND (ur.[RightsName]='Passes - Authorize') " & _
                                  "ORDER BY u.UserName")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim u As New UserType
                    u.UserId = row("UserId")
                    If IsDBNull(row("ProviderUserKey")) Then
                        u.ProviderUserKey = ""
                    Else
                        u.ProviderUserKey = row("ProviderUserKey")
                    End If
                    u.UserName = row("UserName")
                    'Dim dt2 As New DataTable
                    'Dim cmd2 As New SqlCommand("SELECT * FROM [UsersRights] WHERE UserId='" & u.UserId.ToString & "' ORDER BY [RightsName]")
                    'cmd2.Connection = con
                    'da = New SqlDataAdapter(cmd2)
                    'da.Fill(dt2)
                    'If dt2.Rows.Count > 0 Then
                    '    For i2 As Integer = 0 To dt2.Rows.Count - 1
                    '        Dim row2 As DataRow = dt2.Rows(i2)
                    '        u.Rights.Add(row2("RightsName"))
                    '    Next
                    'End If
                    res.Add(u)
                Next
            End If
            con.Close()
        End Using

        Return res

    End Function
    Public Sub AddUser(user As UserType)
        If user Is Nothing Then
            Throw New DataValidationException("The user information parsed is null")
        Else
            If user.UserName = "" Then
                Throw New DataValidationException("Supplied user name is empty, a unique user name must be supplied")
            End If
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()
                Dim trans As SqlTransaction = con.BeginTransaction

                cmd.Connection = con
                cmd.Transaction = trans
                cmd.CommandType = CommandType.Text

                'add user
                cmd.CommandText = "INSERT INTO [Users] VALUES (NEWID(),'" & user.ProviderUserKey & "','" & user.UserName & "')"
                cmd.ExecuteNonQuery()

                'get the new userid
                cmd.CommandText = "SELECT [UserId] " & _
                                  "FROM [Users] " & _
                                  "WHERE [UserName] = '" & user.UserName & "'"
                Dim uid As Guid = cmd.ExecuteScalar

                'flush existing rights
                cmd.CommandText = "DELETE FROM [UsersRights] WHERE [UserId]='" & uid.ToString & "'"
                cmd.ExecuteNonQuery()

                'addup new rights
                For Each r As String In user.Rights
                    cmd.CommandText = "INSERT INTO [UsersRights]([UserId],[RightsName]) " & _
                                      "VALUES('" & uid.ToString & "','" & r & "')"
                    cmd.ExecuteNonQuery()
                Next

                trans.Commit()
                con.Close()
            End Using
        End If
    End Sub
    Public Sub UpdateUser(user As UserType)
        If user Is Nothing Then
            Throw New DataValidationException("The user information parsed is null")
        Else
            If user.UserName = "" Then
                Throw New DataValidationException("Supplied user name is empty, a unique user name must be supplied")
            End If
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()
                Dim trans As SqlTransaction = con.BeginTransaction

                cmd.Connection = con
                cmd.Transaction = trans
                cmd.CommandType = CommandType.Text

                'update user
                cmd.CommandText = "UPDATE [Users] " & _
                                  "SET [ProviderUserKey]='" & user.ProviderUserKey & "' " & _
                                  "   ,[UserName]='" & user.UserName & "' " & _
                                  "WHERE [UserId]='" & user.UserId.ToString & "'"
                cmd.ExecuteNonQuery()

                'flush existing rights
                cmd.CommandText = "DELETE FROM [UsersRights] WHERE [UserId]='" & user.UserId.ToString & "'"
                cmd.ExecuteNonQuery()

                'addup new rights
                For Each r As String In user.Rights
                    cmd.CommandText = "INSERT INTO [UsersRights]([UserId],[RightsName]) " & _
                                      "VALUES('" & user.UserId.ToString & "','" & r & "')"
                    cmd.ExecuteNonQuery()
                Next

                trans.Commit()
                con.Close()
            End Using
        End If
    End Sub
    Public Sub DeleteUser(user As UserType)

        If user Is Nothing Then
            Throw New DataValidationException("The user information parsed is null")
        Else
            If user.UserId = Guid.Empty Then
                Throw New DataValidationException("Supplied user id is empty")
            End If
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()
                Dim trans As SqlTransaction = con.BeginTransaction

                cmd.Connection = con
                cmd.Transaction = trans
                cmd.CommandType = CommandType.Text

                'flush existing rights
                cmd.CommandText = "DELETE FROM [UsersRights] WHERE [UserId]='" & user.UserId.ToString & "'"
                cmd.ExecuteNonQuery()

                'delete user
                cmd.CommandText = "DELETE FROM [Users] WHERE [UserId]='" & user.UserId.ToString & "'"
                cmd.ExecuteNonQuery()

                trans.Commit()
                con.Close()
            End Using
        End If

    End Sub
#End Region

#Region "Passes"
    Private Function GetPass(row As DataRow) As PassType

        Dim res As New PassType
        If IsDBNull(row("PassId")) Then
            res.PassId = Guid.Empty
        Else
            res.PassId = row("PassId")
        End If
        If IsDBNull(row("PassType")) Then
            res.PassType = ""
        Else
            res.PassType = row("PassType")
        End If
        If IsDBNull(row("PassNumber")) Then
            res.PassNumber = ""
        Else
            res.PassNumber = row("PassNumber")
        End If
        If res.PassId = Guid.Empty Then
            Return Nothing
        Else
            Return res
        End If


    End Function
    Public Function GetPassById(PassId As Guid) As PassType

        Dim dt As New DataTable
        Dim res As New PassType
        Dim cmd As New SqlCommand("SELECT * FROM [Passes] WHERE [PassId]='" & PassId.ToString & "'")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                res = GetPass(dt.Rows(0))
            End If
            con.Close()
        End Using

        Return res
    End Function
    Public Function GetPassesList() As Collection(Of PassType)

        Dim dt As New DataTable
        Dim res As New Collection(Of PassType)
        Dim cmd As New SqlCommand("SELECT * FROM [Passes] ORDER BY [PassType],[PassNumber]")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim p As PassType = GetPass(row)
                    If p IsNot Nothing Then res.Add(p)
                Next
            End If

            con.Close()
        End Using

        Return res
    End Function
    Public Function GetPassesList_Available() As Collection(Of PassType)

        Dim dt As New DataTable
        Dim res As New Collection(Of PassType)
        Dim cmd As New SqlCommand("SELECT p.* FROM [Passes] AS p LEFT OUTER JOIN AccessRecords AS ar ON (ar.PassId = p.PassId) AND (ar.RetrievedByUserId IS NULL) WHERE ar.Id IS NULL ORDER BY p.[PassType],p.[PassNumber]")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim p As PassType = GetPass(row)
                    If p IsNot Nothing Then res.Add(p)
                Next
            End If

            con.Close()
        End Using

        Return res
    End Function
    Public Sub AddPass(pass As PassType)

        If pass Is Nothing Then
            Throw New DataValidationException("The pass information parsed is null")
        Else
            If pass.PassNumber = "" Then
                Throw New DataValidationException("Pass number is empty")
            End If

            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()

                cmd.Connection = con
                cmd.CommandType = CommandType.Text

                'add pass
                cmd.CommandText = "INSERT INTO [Passes] " & _
                    "VALUES (NEWID(),'" & pass.PassType & "','" & pass.PassNumber & "')"
                cmd.ExecuteNonQuery()

                con.Close()
            End Using
        End If

    End Sub
    Public Sub UpdatePass(pass As PassType)
        If pass Is Nothing Then
            Throw New DataValidationException("The pass information parsed is null")
        Else
            'validation
            If pass.PassNumber = "" Then
                Throw New DataValidationException("Supplied user name is empty, a user name must be supplied")
            End If

            'db update
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()

                cmd.Connection = con
                cmd.CommandType = CommandType.Text

                'update pass
                cmd.CommandText = "UPDATE [Passes] " & _
                                  "      SET [PassType] = '" & pass.PassType & "' " & _
                                  "         ,[PassNumber]='" & pass.PassNumber & "' " & _
                                  "WHERE [PassId] = '" & pass.PassId.ToString & "'"
                cmd.ExecuteNonQuery()

                con.Close()
            End Using
        End If
    End Sub
    Public Sub DeletePass(pass As PassType)
        If pass Is Nothing Then
            Throw New DataValidationException("The pass information parsed is null")
        Else
            'db update
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()

                cmd.Connection = con
                cmd.CommandType = CommandType.Text

                'delete pass
                cmd.CommandText = "DELETE FROM [Passes] " & _
                                  "WHERE [PassId] = '" & pass.PassId.ToString & "'"
                cmd.ExecuteNonQuery()

                con.Close()
            End Using
        End If
    End Sub
#End Region

#Region "Countries"
    Public Function GetCountriesList() As Collection(Of CountryType)

        Dim dt As New DataTable
        Dim res As New Collection(Of CountryType)
        Dim cmd As New SqlCommand("SELECT * FROM Countries ORDER BY [Name]")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim c As New CountryType
                    If IsDBNull(row("Name")) Then
                        c.Name = ""
                    Else
                        c.Name = row("Name")
                    End If
                    If c.Name <> "" Then res.Add(c)
                Next
            End If
            con.Close()
        End Using
        Return res

    End Function
#End Region

#Region "Customers"
    Private Function GetCustomer(row As DataRow) As CustomerType

        Dim res As New CustomerType
        With res
            If IsDBNull(row("CustomerId")) Then
                .CustomerId = Guid.Empty
            Else
                .CustomerId = row("CustomerId")
            End If
            If IsDBNull(row("IdType")) Then
                .IdType = ""
            Else
                .IdType = row("IdType")
            End If
            If IsDBNull(row("IdNumber")) Then
                .IdNumber = ""
            Else
                .IdNumber = row("IdNumber")
            End If
            If IsDBNull(row("Name")) Then
                .Name = ""
            Else
                .Name = row("Name")
            End If
            If IsDBNull(row("Country")) Then
                .Country = ""
            Else
                .Country = row("Country")
            End If
            If IsDBNull(row("Telephone")) Then
                .Telephone = ""
            Else
                .Telephone = row("Telephone")
            End If
        End With
        If res.CustomerId = Guid.Empty Then
            Return Nothing
        Else
            Return res
        End If

    End Function
    Public Function GetCustomerById(CustomerId As Guid) As CustomerType

        Dim dt As New DataTable
        Dim res As CustomerType = Nothing
        Dim cmd As New SqlCommand("SELECT * FROM [Customers] WHERE [CustomerId]='" & CustomerId.ToString & "'")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                res = GetCustomer(dt.Rows(0))
            End If
            con.Close()
        End Using

        Return res
    End Function
    Public Function GetCustomersList() As Collection(Of CustomerType)

        Dim dt As New DataTable
        Dim res As New Collection(Of CustomerType)
        Dim cmd As New SqlCommand("SELECT * FROM [Customers] ORDER BY [Country],[Name]")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim c As CustomerType = GetCustomer(row)
                    If c IsNot Nothing Then res.Add(c)
                Next
            End If
            con.Close()
        End Using

        Return res
    End Function
    Public Sub AddCustomer(customer As CustomerType)

        If customer Is Nothing Then
            Throw New DataValidationException("The customer information parsed is null")
        Else
            If customer.IdNumber = "" Then
                Throw New DataValidationException("Customer Id number is empty")
            End If
            If customer.Name = "" Then
                Throw New DataValidationException("Customer name is empty")
            End If
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()

                cmd.Connection = con
                cmd.CommandType = CommandType.Text

                'add pass
                cmd.CommandText = "INSERT INTO [Customers] " & _
                                  "VALUES (NEWID(),'" & customer.IdType & _
                                  "','" & customer.IdNumber & _
                                  "','" & customer.Name & _
                                  "','" & customer.Country & _
                                  "','" & customer.Telephone & "')"
                cmd.ExecuteNonQuery()

                con.Close()
            End Using
        End If

    End Sub
    Public Sub UpdateCustomer(customer As CustomerType)

        If customer Is Nothing Then
            Throw New DataValidationException("The customer information parsed is null")
        Else
            If customer.IdNumber = "" Then
                Throw New DataValidationException("Customer Id number is empty")
            End If
            If customer.Name = "" Then
                Throw New DataValidationException("Customer name is empty")
            End If
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()

                cmd.Connection = con
                cmd.CommandType = CommandType.Text

                'add pass
                cmd.CommandText = "UPDATE [Customers] " & _
                                  "SET IdType='" & customer.IdType & "'" & _
                                  "   ,IdNumber='" & customer.IdNumber & "'" & _
                                  "   ,Name='" & customer.Name & "'" & _
                                  "   ,Country='" & customer.Country & "'" & _
                                  "   ,Telephone='" & customer.Telephone & "' " & _
                                  "WHERE [CustomerId]='" & customer.CustomerId.ToString & "'"
                cmd.ExecuteNonQuery()

                con.Close()
            End Using
        End If

    End Sub
    Public Sub DeleteCustomer(customer As CustomerType)

        If customer Is Nothing Then
            Throw New DataValidationException("The customer information parsed is null")
        Else
            If customer.CustomerId = Guid.Empty Then
                Throw New DataValidationException("Customer does not exist")
            End If
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()

                cmd.Connection = con
                cmd.CommandType = CommandType.Text

                'add pass
                cmd.CommandText = "DELETE FROM [Customers] " & _
                                  "WHERE [CustomerId]='" & customer.CustomerId.ToString & "'"
                cmd.ExecuteNonQuery()

                con.Close()
            End Using
        End If

    End Sub
#End Region

#Region "AccessRecords"
    Private Function GetAccessRecord(row As DataRow) As AccessRecordType

        Dim res As New AccessRecordType
        With res
            If IsDBNull(row("Id")) Then
                .Id = Guid.Empty
            Else
                .Id = row("Id")
            End If
            If IsDBNull(row("CustomerId")) Then
                .CustomerId = Guid.Empty
            Else
                .CustomerId = row("CustomerId")
            End If
            If IsDBNull(row("CustomerIdNumber")) Then
                .CustomerIdNumber = ""
            Else
                .CustomerIdNumber = row("CustomerIdNumber")
            End If
            If IsDBNull(row("CustomerName")) Then
                .CustomerName = ""
            Else
                .CustomerName = row("CustomerName")
            End If
            If IsDBNull(row("CustomerPhone")) Then
                .CustomerPhone = ""
            Else
                .CustomerPhone = row("CustomerPhone")
            End If
            If IsDBNull(row("PassId")) Then
                .PassId = Guid.Empty
            Else
                .PassId = row("PassId")
            End If
            If IsDBNull(row("IssuedPassNo")) Then
                .IssuedPassNo = ""
            Else
                .IssuedPassNo = row("IssuedPassNo")
            End If
            If IsDBNull(row("PassSerialNumber")) Then
                .PassSerialNumber = ""
            Else
                .PassSerialNumber = row("PassSerialNumber")
            End If
            If IsDBNull(row("Purpose")) Then
                .Purpose = ""
            Else
                .Purpose = row("Purpose")
            End If
            If IsDBNull(row("AuthorizedByUserId")) Then
                .AuthorizedByUserId = Guid.Empty
            Else
                .AuthorizedByUserId = row("AuthorizedByUserId")
            End If
            If IsDBNull(row("AuthorizedByUser")) Then
                .AuthorizedByUser = ""
            Else
                .AuthorizedByUser = row("AuthorizedByUser")
            End If
            If IsDBNull(row("IssuedByUserId")) Then
                .IssuedByUserId = Guid.Empty
            Else
                .IssuedByUserId = row("IssuedByUserId")
            End If
            If IsDBNull(row("IssuedByUser")) Then
                .IssuedByUser = ""
            Else
                .IssuedByUser = row("IssuedByUser")
            End If
            If IsDBNull(row("IssuedDate")) Then
                .IssuedDate = DateTime.MinValue
            Else
                .IssuedDate = row("IssuedDate")
            End If
            If IsDBNull(row("RetrievedByUserId")) Then
                .RetrievedByUserId = Guid.Empty
            Else
                .RetrievedByUserId = row("RetrievedByUserId")
            End If
            If IsDBNull(row("RetrievedByUser")) Then
                .RetrievedByUser = ""
            Else
                .RetrievedByUser = row("RetrievedByUser")
            End If
            If IsDBNull(row("RetrievedDate")) Then
                .RetrievedDate = DateTime.MinValue
            Else
                .RetrievedDate = row("RetrievedDate")
            End If
            If IsDBNull(row("Status")) Then
                .Status = DateTime.MinValue
            Else
                .Status = row("Status")
            End If
        End With
        If res.CustomerId = Guid.Empty Then
            Return Nothing
        Else
            Return res
        End If

    End Function
    Public Function GetAccessRecordById(Id As Guid) As AccessRecordType

        Dim dt As New DataTable
        Dim res As AccessRecordType = Nothing
        Dim cmd As New SqlCommand("SELECT * FROM [AccessRecords] WHERE [Id]='" & Id.ToString & "'")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                res = GetAccessRecord(dt.Rows(0))
            End If
            con.Close()
        End Using

        Return res
    End Function
    Public Function GetAccessRecordList_Pending() As DataTable

        Dim dt As New DataTable
        Dim cmd As New SqlCommand("SELECT * " & _
                                  "FROM [AccessRecords] " & _
                                  "WHERE [Status] = 'Issued' " & _
                                  "ORDER BY [IssuedDate] DESC")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            con.Close()
        End Using

        Return dt
    End Function
    Public Function GetAccessRecordList(CustomerId As Guid, fromDate As DateTime, toDate As DateTime) As DataTable

        Dim dt As New DataTable
        Dim cmd As New SqlCommand("SELECT * " & _
                                  "FROM [AccessRecords] " & _
                                  "WHERE " & _
                                  "     ([CustomerId] = '" & CustomerId.ToString & "') " & _
                                  " AND ([IssuedDate] BETWEEN '" & fromDate.ToString("yyyy-MM-dd") & "' " & _
                                  "                       AND '" & toDate.ToString("yyyy-MM-dd") & "') " & _
                                  "ORDER BY [IssuedDate] DESC")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            con.Close()
        End Using

        Return dt
    End Function
    Public Function GetAccessRecordList(CustomerId As Guid) As DataTable

        Dim dt As New DataTable
        Dim cmd As New SqlCommand("SELECT * " & _
                                  "FROM [AccessRecords] " & _
                                  "WHERE " & _
                                  "     ([CustomerId] = '" & CustomerId.ToString & "') " & _
                                  "ORDER BY [IssuedDate] DESC")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            con.Close()
        End Using

        Return dt
    End Function
    Public Function GetAccessRecordList(fromDate As DateTime, toDate As DateTime) As DataTable

        Dim dt As New DataTable
        Dim cmd As New SqlCommand("SELECT * " & _
                                  "FROM [AccessRecords] " & _
                                  "WHERE " & _
                                  "     ([IssuedDate] BETWEEN '" & fromDate.ToString("yyyy-MM-dd") & "' " & _
                                  "                       AND '" & toDate.ToString("yyyy-MM-dd") & "') " & _
                                  "ORDER BY [IssuedDate] DESC")
        Dim da As New SqlDataAdapter(cmd)
        Using con As New SqlConnection(DbHelper.GetConnectionString)
            con.Open()
            cmd.Connection = con
            da.Fill(dt)
            con.Close()
        End Using

        Return dt
    End Function
    Private Sub SetupARParams(cmd As SqlCommand, ar As AccessRecordType)
        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("@Id", ar.Id)
        If ar.CustomerId = Guid.Empty Then
            cmd.Parameters.AddWithValue("@CustomerId", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@CustomerId", ar.CustomerId)
        End If
        If ar.CustomerIdNumber = "" Then
            cmd.Parameters.AddWithValue("@CustomerIdNumber", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@CustomerIdNumber", ar.CustomerIdNumber)
        End If
        If ar.CustomerName = "" Then
            cmd.Parameters.AddWithValue("@CustomerName", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@CustomerName", ar.CustomerName)
        End If
        If ar.CustomerPhone = "" Then
            cmd.Parameters.AddWithValue("@CustomerPhone", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@CustomerPhone", ar.CustomerPhone)
        End If
        If ar.PassId = Guid.Empty Then
            cmd.Parameters.AddWithValue("@PassId", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@PassId", ar.PassId)
        End If
        If ar.IssuedPassNo = "" Then
            cmd.Parameters.AddWithValue("@IssuedPassNo", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@IssuedPassNo", ar.IssuedPassNo)
        End If
        If ar.PassSerialNumber = "" Then
            cmd.Parameters.AddWithValue("@PassSerialNumber", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@PassSerialNumber", ar.PassSerialNumber)
        End If
        If ar.Purpose = "" Then
            cmd.Parameters.AddWithValue("@Purpose", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@Purpose", ar.Purpose)
        End If
        If ar.AuthorizedByUserId = Guid.Empty Then
            cmd.Parameters.AddWithValue("@AuthorizedByUserId", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@AuthorizedByUserId", ar.AuthorizedByUserId)
        End If
        If ar.AuthorizedByUser = "" Then
            cmd.Parameters.AddWithValue("@AuthorizedByUser", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@AuthorizedByUser", ar.AuthorizedByUser)
        End If
        If ar.IssuedByUserId = Guid.Empty Then
            cmd.Parameters.AddWithValue("@IssuedByUserId", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@IssuedByUserId", ar.IssuedByUserId)
        End If
        If ar.IssuedByUser = "" Then
            cmd.Parameters.AddWithValue("@IssuedByUser", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@IssuedByUser", ar.IssuedByUser)
        End If
        If ar.IssuedDate = DateTime.MinValue Then
            cmd.Parameters.AddWithValue("@IssuedDate", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@IssuedDate", ar.IssuedDate)
        End If
        If ar.RetrievedByUserId = Guid.Empty Then
            cmd.Parameters.AddWithValue("@RetrievedByUserId", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@RetrievedByUserId", ar.RetrievedByUserId)
        End If
        If ar.RetrievedByUser = "" Then
            cmd.Parameters.AddWithValue("@RetrievedByUser", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@RetrievedByUser", ar.RetrievedByUser)
        End If
        If ar.RetrievedDate = DateTime.MinValue Then
            cmd.Parameters.AddWithValue("@RetrievedDate", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@RetrievedDate", ar.RetrievedDate)
        End If
        If ar.Status = "" Then
            cmd.Parameters.AddWithValue("@Status", System.DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@Status", ar.Status)
        End If
    End Sub
    Public Sub IssuePass(ar As AccessRecordType)

        If ar Is Nothing Then
            Throw New DataValidationException("The access record is empty")
        Else
            'VALIDATION
            If ar.PassId = Guid.Empty Then
                Throw New DataValidationException("Pass selection is empty")
            End If
            If ar.CustomerId = Guid.Empty Then
                Throw New DataValidationException("Customer selection is empty")
            End If
            If ar.AuthorizedByUserId = Guid.Empty Then
                Throw New DataValidationException("Authorized by user selection is empty")
            End If

            'DEFAULTS
            Dim customer As CustomerType = Me.GetCustomerById(ar.CustomerId)
            ar.CustomerIdNumber = customer.IdNumber
            ar.CustomerName = customer.Name
            ar.CustomerPhone = customer.Telephone

            Dim pass As PassType = Me.GetPassById(ar.PassId)
            ar.IssuedPassNo = pass.PassNumber

            Dim authUser As UserType = Me.GetUserById(ar.AuthorizedByUserId)
            ar.AuthorizedByUser = authUser.UserName

            Dim issuUser As UserType = Me.GetUserById(ar.IssuedByUserId)
            ar.IssuedByUser = issuUser.UserName

            ar.Id = Guid.NewGuid
            ar.RetrievedByUserId = Guid.Empty
            ar.RetrievedDate = DateTime.MinValue
            ar.Status = "Pass Issued"

            'DATABASE WORK
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()
                Dim trans As SqlTransaction = con.BeginTransaction

                cmd.Connection = con
                cmd.Transaction = trans
                cmd.CommandType = CommandType.Text

                'get pass serial no. and increment it
                cmd.CommandText = "SELECT [Value] FROM [Configs] WHERE [Name]='PassSerialNumber'"
                Dim ser As Integer = cmd.ExecuteScalar
                ser += 1
                cmd.CommandText = "UPDATE [Configs] SET [Value]='" & ser.ToString & "' WHERE [Name]='PassSerialNumber'"
                cmd.ExecuteNonQuery()
                ar.PassSerialNumber = Format(ser, "000000")
                ar.Status = "Issued"

                'add pass
                cmd.CommandText = "INSERT INTO [AccessRecords] " & _
                                  "VALUES(@Id " & _
                                  "		,@CustomerId " & _
                                  "     ,@CustomerIdNumber " & _
                                  "     ,@CustomerName " & _
                                  "     ,@CustomerPhone " & _
                                  "		,@PassId " & _
                                  "     ,@IssuedPassNo " & _
                                  "		,@PassSerialNumber " & _
                                  "		,@Purpose " & _
                                  "		,@AuthorizedByUserId " & _
                                  "     ,@AuthorizedByUser " & _
                                  "		,@IssuedByUserId " & _
                                  "     ,@IssuedByUser " & _
                                  "		,@IssuedDate " & _
                                  "		,@RetrievedByUserId " & _
                                  "     ,@RetrievedByUser " & _
                                  "		,@RetrievedDate " & _
                                  "     ,@Status) "
                SetupARParams(cmd, ar)
                cmd.ExecuteNonQuery()

                trans.Commit()
                con.Close()
            End Using
        End If

    End Sub
    Public Sub RetrievePass(ar As AccessRecordType)

        If ar Is Nothing Then
            Throw New DataValidationException("The access record is empty")
        Else
            'VALIDATION
            If ar.PassId = Guid.Empty Then
                Throw New DataValidationException("Pass selection is empty")
            End If
            If ar.CustomerId = Guid.Empty Then
                Throw New DataValidationException("Customer selection is empty")
            End If
            If ar.AuthorizedByUserId = Guid.Empty Then
                Throw New DataValidationException("Authorized by user selection is empty")
            End If

            'DATABASE WORK
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()
                Dim trans As SqlTransaction = con.BeginTransaction

                cmd.Connection = con
                cmd.Transaction = trans
                cmd.CommandType = CommandType.Text

                'setup defaults
                Dim retvUser As UserType = Me.GetUserById(ar.RetrievedByUserId)
                ar.RetrievedByUser = retvUser.UserName

                ar.Status = "Retreived"

                'add pass
                cmd.CommandText = "UPDATE [AccessRecords] " & _
                                  "SET   CustomerId         = @CustomerId " & _
                                  "		,CustomerIdNumber   = @CustomerIdNumber " & _
                                  "		,CustomerName       = @CustomerName " & _
                                  "		,CustomerPhone      = @CustomerPhone " & _
                                  "		,PassId             = @PassId " & _
                                  "		,IssuedPassNo       = @IssuedPassNo " & _
                                  "		,PassSerialNumber   = @PassSerialNumber " & _
                                  "		,Purpose            = @Purpose " & _
                                  "		,AuthorizedByUserId = @AuthorizedByUserId " & _
                                  "		,AuthorizedByUser   = @AuthorizedByUser " & _
                                  "		,IssuedByUserId     = @IssuedByUserId " & _
                                  "		,IssuedByUser       = @IssuedByUser " & _
                                  "		,IssuedDate         = @IssuedDate " & _
                                  "		,RetrievedByUserId  = @RetrievedByUserId " & _
                                  "		,RetrievedByUser    = @RetrievedByUser " & _
                                  "		,RetrievedDate      = @RetrievedDate " & _
                                  "		,Status             = @Status " & _
                                  "WHERE Id=@Id"
                SetupARParams(cmd, ar)
                cmd.ExecuteNonQuery()

                trans.Commit()
                con.Close()
            End Using
        End If


    End Sub
    Public Sub LostPass(ar As AccessRecordType)

        If ar Is Nothing Then
            Throw New DataValidationException("The access record is empty")
        Else
            'VALIDATION
            If ar.PassId = Guid.Empty Then
                Throw New DataValidationException("Pass selection is empty")
            End If
            If ar.CustomerId = Guid.Empty Then
                Throw New DataValidationException("Customer selection is empty")
            End If
            If ar.AuthorizedByUserId = Guid.Empty Then
                Throw New DataValidationException("Authorized by user selection is empty")
            End If

            'DATABASE WORK
            Dim cmd As New SqlCommand
            Using con As New SqlConnection(DbHelper.GetConnectionString)
                con.Open()
                Dim trans As SqlTransaction = con.BeginTransaction

                cmd.Connection = con
                cmd.Transaction = trans
                cmd.CommandType = CommandType.Text

                'setup defaults
                Dim retvUser As UserType = Me.GetUserById(ar.RetrievedByUserId)
                ar.RetrievedByUser = retvUser.UserName

                ar.Status = "Lost"

                'add pass
                cmd.CommandText = "UPDATE [AccessRecords] " & _
                                  "SET   CustomerId         = @CustomerId " & _
                                  "		,CustomerIdNumber   = @CustomerIdNumber " & _
                                  "		,CustomerName       = @CustomerName " & _
                                  "		,CustomerPhone      = @CustomerPhone " & _
                                  "		,PassId             = @PassId " & _
                                  "		,IssuedPassNo       = @IssuedPassNo " & _
                                  "		,PassSerialNumber   = @PassSerialNumber " & _
                                  "		,Purpose            = @Purpose " & _
                                  "		,AuthorizedByUserId = @AuthorizedByUserId " & _
                                  "		,AuthorizedByUser   = @AuthorizedByUser " & _
                                  "		,IssuedByUserId     = @IssuedByUserId " & _
                                  "		,IssuedByUser       = @IssuedByUser " & _
                                  "		,IssuedDate         = @IssuedDate " & _
                                  "		,RetrievedByUserId  = @RetrievedByUserId " & _
                                  "		,RetrievedByUser    = @RetrievedByUser " & _
                                  "		,RetrievedDate      = @RetrievedDate " & _
                                  "		,Status             = @Status " & _
                                  "WHERE Id=@Id"
                SetupARParams(cmd, ar)
                cmd.ExecuteNonQuery()

                trans.Commit()
                con.Close()
            End Using
        End If


    End Sub

#End Region

End Class

Public Class DataValidationException
    Inherits Exception

    Public Sub New(Message As String)
        MyBase.New(Message)
    End Sub

    Public Sub New(Message As String, innerException As Exception)
        MyBase.New(Message, innerException)
    End Sub


End Class